package com.ssy.lingxi.report.dao.enhance;

import com.ssy.lingxi.report.entity.settleaccount.MemberProductionNoticeSettlementDO;
import com.ssy.lingxi.report.model.bo.MemberSettlementBO;
import com.ssy.lingxi.report.model.bo.StatisticsCountBO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 生产通知单DAO
 * @author 伍将
 * @date 2020/11/26
 * @version 2.0.0
 */
@Service
public class ProduceNoticeOrderDAO {

    @Autowired
    @Qualifier("enhanceJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询供应商生产通知单统计计数
     * @author 伍将
     * @date 2020/11/26
     * @return:
     **/
    public List<StatisticsCountBO> getSupplierStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" supplier_member_id member_id,supplier_role_id role_id,status status_type,count(id)")
                .append(" from enhance_produce_notice_order")
                .append(" where status in(1,2,3,4,15,17)")
                .append(" group by supplier_Member_id,supplier_role_id,status;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsCountBO.class));
    }

    /**
     * 查询加工企业生产通知单统计计数
     * @author 伍将
     * @date 2020/11/26
     * @return:
     **/
    public List<StatisticsCountBO> getProcessStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" process_member_id member_id,process_role_id role_id,status status_type,count(id)")
                .append(" from enhance_produce_notice_order")
                .append(" where status in(5,6,7,8,10,12,14,18)")
                .append(" group by process_Member_id,process_role_id,status;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(StatisticsCountBO.class));
    }

    /**
     * 获取生产通知单结算数据
     * @author 伍将
     * @date 2020/11/27
     * @param startTime: 开始时间
     * @param endTime: 结束时间
     * @param payMemberId: 支付会员id（供应商）
     * @param payRoleId: 支付会员角色id（供应商）
     * @param memberId: 收款会员id（加工企业）
     * @param roleId: 收款会员角色id（加工企业）
     * @return:
     **/
    public MemberSettlementBO getSettlement(Long startTime, Long endTime, Long payMemberId, Long payRoleId, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" count(detail.id) total_count,COALESCE(sum(detail.receive_price),0) total_amount")
                .append(" from enhance_pno_settle_detail detail")
                .append(" inner join enhance_produce_notice_order ord")
                .append(" on ord.id=detail.produce_notice_order_id")
                .append(" where receive_time between " + startTime + " and " + endTime)    // 收货时间范围
                .append(" and ord.supplier_member_id=" + payMemberId + " and ord.supplier_role_id=" + payRoleId)    // 付款方
                .append(" and ord.process_member_id=" + memberId + " and ord.process_role_id=" + roleId + " ;");    // 收款方

        List<MemberSettlementBO> resultList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberSettlementBO.class));

        // 剔除订单数与运费总额全为0的数据
        return resultList.stream().filter(o -> o.getTotalAmount() > 0).findFirst().orElse(null);
    }


    /**
     * 获取生产通知单结算明细
     * @param startTime
     * @param endTime
     * @param payMemberId
     * @param payRoleId
     * @param memberId
     * @param roleId
     * @return
     */
    public List<MemberProductionNoticeSettlementDO> getSettlementDetail(Long startTime, Long endTime, Long payMemberId, Long payRoleId, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" detail.notice_no orderNo,detail.summary orderAbstract,produce_notice_order_time orderTime,batch,")
                .append(" receive_time,receive_num receiveCount,receive_price settlement_amount,pno_receive_deliver_detail_id dataId,")
                .append(" is_has_tax ,tax_rate ")
                .append(" from enhance_pno_settle_detail detail")
                .append(" inner join enhance_produce_notice_order ord")
                .append(" on ord.id=detail.produce_notice_order_id")
                .append(" where receive_time between " + startTime + " and " + endTime)    // 收货时间范围
                .append(" and ord.supplier_member_id=" + payMemberId + " and ord.supplier_role_id=" + payRoleId)    // 付款方
                .append(" and ord.process_member_id=" + memberId + " and ord.process_role_id=" + roleId + " ;");    // 收款方

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberProductionNoticeSettlementDO.class));
    }
}
